package com.github.dengmin.mysql.service;

import com.github.dengmin.mysql.common.ConnectionPool;
import com.github.dengmin.mysql.common.SQL;
import com.github.dengmin.mysql.dto.ExecuteSql;
import com.github.dengmin.mysql.model.DatabaseConnection;
import com.github.dengmin.mysql.model.TableColumn;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.StatementCallback;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

/**
 * @Author dengmin
 * @Created 2020/6/19 上午11:16
 */
@Service
public class SQLExecute {
    private static final long serialVersionUID = 1L;
    @Resource
    private DatabaseConnectionService databaseConnectionService;
    /**
     * 执行sql没有结果集
     * @param sqlObj
     */
    public Map<String,Object> execute(ExecuteSql sqlObj){
        DatabaseConnection connection = databaseConnectionService.getById(sqlObj.getResourceId());
        DataSource dataSource = ConnectionPool.create(connection);
        JdbcTemplate jdbc = new JdbcTemplate(dataSource);
        //执行sql 返回受影响的行数
        String sql = sqlObj.getSql();
        int pageNo = sqlObj.getPageNo();
        int pageSize = sqlObj.getPageSize();
        int limit = (pageNo - 1) * pageSize;

        //判断是不是查询语句
        String[] STARTS = new String[]{"select", "SELECT"};
        boolean isSelect = Arrays.stream(STARTS).anyMatch(sql::startsWith);
        if(isSelect) {
            //分页查询
            sql = String.format("SELECT * FROM (%S) tab LIMIT %d, %d", sql, limit, pageSize);
        }
        String finalSql = sql;
        return jdbc.execute(new StatementCallback<Map<String,Object>>(){
            @Override
            public Map<String,Object> doInStatement(Statement statement) throws SQLException, DataAccessException {
                long beginTime = System.currentTimeMillis();
                int total = getCount(jdbc, sqlObj.getSql());
                boolean hasResultSet = statement.execute(finalSql);
                long spendTime = System.currentTimeMillis() - beginTime;

                Map<String, Object> results = new HashMap<>();

                //sql 执行时间
                results.put("spendTime", spendTime);
                if(hasResultSet){
                    results.put("withResults", true);
                    // 列
                    List<TableColumn> columns = new ArrayList<>();
                    //数据
                    List<Map<String,Object>> rows = new ArrayList<>();
                    ResultSet rs = statement.getResultSet();
                    ResultSetMetaData metaData = rs.getMetaData();
                    for(int i = 1; i <= metaData.getColumnCount(); i++){
                        TableColumn column = new TableColumn();
                        column.setName(metaData.getColumnName(i));
                        column.setDataType(metaData.getColumnTypeName(i));
                        columns.add(column);
                    }
                    while (rs.next()){
                        Map<String, Object> data = new HashMap<>();
                        for(int i=1; i<= metaData.getColumnCount(); i++){
                            String dataType = metaData.getColumnTypeName(i);
                            if("DATETIME".equals(dataType)){
                                if(rs.getTimestamp(i) != null) {
                                    data.put(metaData.getColumnName(i), new Date(rs.getTimestamp(i).getTime()));
                                }else{
                                    data.put(metaData.getColumnName(i), null);
                                }
                            }else {
                                data.put(metaData.getColumnName(i), rs.getObject(i));
                            }
                        }
                        rows.add(data);
                    }
                    results.put("columns", columns);
                    results.put("rows", rows);
                    results.put("total", total);
                }else{
                    results.put("withResults", false);
                    results.put("count", statement.getUpdateCount());
                }
                return results;
            }
        });
    }


    public Integer getCount(JdbcTemplate jdbcTemplate, String sql){
        return jdbcTemplate.execute(new StatementCallback<Integer>() {
            @Override
            public Integer doInStatement(Statement statement) throws SQLException, DataAccessException {
                ResultSet rs = statement.executeQuery(sql);
                rs.last();
                return rs.getRow();
            }
        });
    }
}
